/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package workforce.limousine.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import limousine.vo.Lookup;
import org.workforce.util.Utils;

/**
 *
 * @author krisada
 */
public class LookupSearcherImpl implements LookupSearcher {
    
    private DataSource dataSource = null;
    
    public LookupSearcherImpl() {
    }

    
     public List<Lookup> searchLookupByGroup(String groupId) {
        List<Lookup> list = new ArrayList<Lookup>();
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            StringBuilder s = new StringBuilder();
            s.append("SELECT ");
            s.append("IFNULL(A.TABLE_ID, '-') AS TABLE_ID,");
            s.append("IFNULL(A.LOOKUP_ID, '-') AS LOOKUP_ID,");
            s.append("IFNULL(A.LOOKUP_NAME, '-') AS LOOKUP_NAME,");
            s.append("IFNULL(A.LOOKUP_DESCRIPTION, '-') AS LOOKUP_DESCRIPTION,");            
            s.append("IFNULL(A.LOOKUP_GROUP_ID, '-')  AS LOOKUP_GROUP_ID,");
            s.append("IFNULL(A.LOOKUP_ORDER, 0) AS LOOKUP_ORDER,");
            s.append("IFNULL(A.LOOKUP_VALUE, '-') AS LOOKUP_VALUE ");
            s.append("FROM lookup A WHERE A.LOOKUP_GROUP_ID = ? ORDER BY A.LOOKUP_ORDER ASC");
            System.out.println("sql["+ s.toString() +"]");
            PreparedStatement stmt = conn.prepareStatement(s.toString());
            stmt.setString(1, groupId);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Lookup lookup = new Lookup();
                lookup.setTableId(rs.getInt(1));
                lookup.setLookupId(Utils.StringField(rs.getString(2)));
                lookup.setLookupName(Utils.StringField(rs.getString(3)));
                lookup.setLookupDescription(Utils.StringField(rs.getString(4)));
                lookup.setLookupGroupId(Utils.StringField(rs.getString(5)));
                lookup.setLookupOrder(rs.getInt(6));
                lookup.setLookupValue(rs.getString(7));
                list.add(lookup);
            }
            rs.close(); 
            rs = null;
            stmt.close();
            stmt = null;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                }
            }
        }
        return list;
    }

    public void setDataSource(DataSource ds) {
        this.dataSource = ds;
    }
    
    
}
